﻿Imports System.Data.SqlClient
Imports System.Data.Odbc

Public Class frmRAbasDiesel
    Dim Bitacora As New clsActividad
    Dim Usuario As New clsUsuarioPrincipal

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Close()

    End Sub
    Sub CargaRutas()
        lstRuta.Items.Clear()

        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection

        Dim strSql As String = ""
        strSql = "select Nombre from Rutas order by Nombre"

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn
        cmdBuscar.CommandText = strSql

        Dim rdBuscar As SqlDataReader
        cnconn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        Do While rdBuscar.Read()
            Me.lstRuta.Items.Add(rdBuscar("Nombre").ToString.Trim)
        Loop
    End Sub
    Sub cargaAutobuses()
        lstAutobus.Items.Clear()

        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection

        Dim strSql As String = ""
        strSql = "select NoEconomico from Autobuses where Estatus=1 order by NoEconomico"

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn
        cmdBuscar.CommandText = strSql

        Dim rdBuscar As SqlDataReader
        cnconn.Open()
        rdBuscar = cmdBuscar.ExecuteReader
        Dim primero As Boolean
        primero = True

        Do While rdBuscar.Read()

            Me.lstAutobus.Items.Add(rdBuscar("NoEconomico").ToString.Trim)
        Loop

    End Sub

    Private Sub RadioButton1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton1.CheckedChanged
        lstA.Enabled = False
        lstAutobus.Enabled = False
        lstR.Enabled = False
        lstRuta.Enabled = False

    End Sub

    Private Sub RadioButton2_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton2.CheckedChanged
        lstA.Enabled = False
        lstAutobus.Enabled = False
        lstR.Enabled = True
        lstRuta.Enabled = True
    End Sub

    Private Sub RadioButton3_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton3.CheckedChanged
        lstA.Enabled = True
        lstAutobus.Enabled = True
        lstR.Enabled = False
        lstRuta.Enabled = False
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim aux As String = ""
        aux = lstRuta.Text.ToString.Trim
        If aux <> "" Then
            lstR.Items.Add(lstRuta.SelectedItem)
            lstRuta.Items.Remove(lstRuta.SelectedItem)
        End If
        

    End Sub

    Private Sub frmRAbasDiesel_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ToolStrip1.Cursor = Cursors.Hand
        ToolStrip1.RenderMode = System.Windows.Forms.ToolStripRenderMode.System

        lstA.Items.Clear()
        lstR.Items.Clear()
        cargaAutobuses()
        CargaRutas()
        RadioButton1.Checked = True


    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim aux As String = ""
        aux = lstR.Text.ToString.Trim
        If aux <> "" Then
            lstRuta.Items.Add(lstR.SelectedItem)
            lstR.Items.Remove(lstR.SelectedItem)
        End If
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Dim aux As String = ""
        aux = lstAutobus.Text.ToString.Trim
        If aux <> "" Then
            lstA.Items.Add(lstAutobus.SelectedItem)
            lstAutobus.Items.Remove(lstAutobus.SelectedItem)
        End If
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        Dim aux As String = ""
        aux = lstA.Text.ToString.Trim
        If aux <> "" Then
            lstAutobus.Items.Add(lstA.SelectedItem)
            lstA.Items.Remove(lstA.SelectedItem)
        End If
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        'Registra actividad
        Bitacora.RegistraActividad("Imprimió el reporte de abastecimientos de diesel del dia " & datFecha1.Text & " al " & datFecha2.Text)
        If RadioButton1.Checked = True Then
            todos()
        ElseIf RadioButton2.Checked = True Then
            ruta()
        Else
            autobus()
        End If

    End Sub
    Sub todos()
        Dim xl As Object
        Dim wb As Object
        Dim i As Integer
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 5

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim NuevaFecha1 As String = datFecha1.Value.ToString("yyyy-dd-MM")
        Dim NuevaFecha2 As String = datFecha2.Value.AddDays(1).ToString("yyyy-dd-MM")
        NuevaFecha1 = NuevaFecha1 & " 00:00:00"
        NuevaFecha2 = NuevaFecha2 & " 00:00:00"

        'Dim strFecha1 As String = Me.datFecha1.Value.ToString("MM/dd/yyyy") 'String = Format(Me.datFecha1.Value.Year, "0000") & Format(Me.datFecha1.Value.Month, "00") & Format(Me.datFecha1.Value.Day, "00")
        'Dim strFecha2 As String = Me.datFecha2.Value.ToString("MM/dd/yyyy") 'String = Format(Me.datFecha2.Value.Year, "0000") & Format(Me.datFecha2.Value.Month, "00") & Format(Me.datFecha2.Value.Day, "00")
        '--------
        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection
        Dim strSql As String = ""
        cnconn.Open()
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn
        Dim cmdAsignar As New SqlCommand
        cmdAsignar.Connection = cnconn

        strSql = "SELECT IdRuta,NoEconomico,SUM(Litros) as Litros, SUM(KilometrosTotales) as Totales, SUM(KilometrosOperacion) as Operacion, SUM(KilometrosMuertos) as Muertos," & _
                 " SUM(SubTotal) as Subtotal, SUM(IVA) AS Iva, SUM(Total) as Total " & _
                 " FROM Diesel " & _
                 " WHERE Fecha>='" & NuevaFecha1 & "' AND Fecha<='" & NuevaFecha2 & "'" & _
                 " GROUP BY NoEconomico,IdRuta " & _
                 " ORDER BY IdRuta,NoEconomico"

        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        rdBuscar = cmdBuscar.ExecuteReader
        Dim strFolio As String = "", strSerie As String = ""
        Dim strFecha As String = ""
        Dim strUnidad As String = ""
        Dim acu(999) As Decimal
        Dim strCamion As String = "?"
        Dim strFechaMov As String = ""

        Dim Litros As Double = 0
        Dim Totales As Double = 0
        Dim Operacion As Double = 0
        Dim Muertos As Double = 0
        Dim SubTotal As Double = 0 '
        Dim IVA As Double = 0
        Dim Total As Double = 0
        Dim LitrosG As Double = 0
        Dim TotalesG As Double = 0
        Dim OperacionG As Double = 0
        Dim MuertosG As Double = 0
        Dim SubTotalG As Double = 0 '
        Dim IVAG As Double = 0
        Dim TotalG As Double = 0



        rng = rng + 1
        Do While rdBuscar.Read()
            If strCamion <> rdBuscar("IdRuta").ToString.Trim Then
                If strCamion <> "?" Then
                    wb.SHEETS(1).cells(rng, 1).value = "Totales"
                    wb.SHEETS(1).cells(rng, 2).value = Litros
                    wb.SHEETS(1).cells(rng, 3).value = Totales
                    wb.SHEETS(1).cells(rng, 4).value = Operacion
                    wb.SHEETS(1).cells(rng, 5).value = Muertos
                    wb.SHEETS(1).cells(rng, 6).value = Totales / Litros
                    wb.SHEETS(1).cells(rng, 7).value = SubTotal
                    wb.SHEETS(1).cells(rng, 8).value = IVA
                    wb.SHEETS(1).cells(rng, 9).value = Total

                    Litros = 0
                    Totales = 0
                    Operacion = 0
                    Muertos = 0
                    SubTotal = 0 '
                    IVA = 0
                    Total = 0
                    rng = rng + 2 ' Separa entre ruta
                End If
                wb.SHEETS(1).cells(rng, 1).value = "Ruta: " & BuscarStrDatoCitra("SELECT Nombre FROM Rutas WHERE IdRuta=" & rdBuscar("IdRuta"))
                wb.SHEETS(1).cells(rng, 1).font.size = 12
                wb.SHEETS(1).cells(rng, 1).font.bold = -1
                rng = rng + 1
                wb.SHEETS(1).cells(rng, 1).value = "Autobus"
                wb.SHEETS(1).cells(rng, 2).value = "Litros"
                wb.SHEETS(1).cells(rng, 3).value = "Kms Totales"
                wb.SHEETS(1).cells(rng, 4).value = "Kms Operacion"
                wb.SHEETS(1).cells(rng, 5).value = "Kms Muertos"
                wb.SHEETS(1).cells(rng, 6).value = "Rendimiento"
                wb.SHEETS(1).cells(rng, 7).value = "SubTotal"
                wb.SHEETS(1).cells(rng, 8).value = "IVA"
                wb.SHEETS(1).cells(rng, 9).value = "Total"

                For i = 1 To 9
                    wb.SHEETS(1).cells(rng, i).font.bold = True
                Next
                rng = rng + 1
            End If
            strCamion = rdBuscar("IdRuta").ToString.Trim

            wb.SHEETS(1).cells(rng, 1).value = rdBuscar("NoEconomico")
            wb.SHEETS(1).cells(rng, 2).value = rdBuscar("Litros")
            wb.SHEETS(1).cells(rng, 3).value = rdBuscar("Totales")
            wb.SHEETS(1).cells(rng, 4).value = rdBuscar("Operacion")
            wb.SHEETS(1).cells(rng, 5).value = rdBuscar("Muertos")
            wb.SHEETS(1).cells(rng, 6).value = wb.SHEETS(1).cells(rng, 3).value / wb.SHEETS(1).cells(rng, 2).value
            wb.SHEETS(1).cells(rng, 7).value = rdBuscar("Subtotal")
            wb.SHEETS(1).cells(rng, 8).value = rdBuscar("IVA")
            wb.SHEETS(1).cells(rng, 9).value = rdBuscar("Total")

            Litros = Litros + wb.SHEETS(1).cells(rng, 2).value
            Totales = Totales + wb.SHEETS(1).cells(rng, 3).value
            Operacion = Operacion + wb.SHEETS(1).cells(rng, 4).value
            Muertos = Muertos + wb.SHEETS(1).cells(rng, 5).value
            SubTotal = SubTotal + wb.SHEETS(1).cells(rng, 7).value
            IVA = IVA + wb.SHEETS(1).cells(rng, 8).value
            Total = Total + wb.SHEETS(1).cells(rng, 9).value

            LitrosG = LitrosG + wb.SHEETS(1).cells(rng, 2).value
            TotalesG = TotalesG + wb.SHEETS(1).cells(rng, 3).value
            OperacionG = OperacionG + wb.SHEETS(1).cells(rng, 4).value
            MuertosG = MuertosG + wb.SHEETS(1).cells(rng, 5).value
            SubTotalG = SubTotalG + wb.SHEETS(1).cells(rng, 7).value
            IVAG = IVAG + wb.SHEETS(1).cells(rng, 8).value
            TotalG = TotalG + wb.SHEETS(1).cells(rng, 9).value

            rng = rng + 1
            'End If
        Loop

        wb.SHEETS(1).cells(rng, 1).value = "Totales"
        wb.SHEETS(1).cells(rng, 2).value = Litros
        wb.SHEETS(1).cells(rng, 3).value = Totales
        wb.SHEETS(1).cells(rng, 4).value = Operacion
        wb.SHEETS(1).cells(rng, 5).value = Muertos
        wb.SHEETS(1).cells(rng, 6).value = Totales / Litros
        wb.SHEETS(1).cells(rng, 7).value = SubTotal
        wb.SHEETS(1).cells(rng, 8).value = IVA
        wb.SHEETS(1).cells(rng, 9).value = Total

        rng = rng + 2

        wb.SHEETS(1).cells(rng, 1).value = "Total General"
        wb.SHEETS(1).cells(rng, 2).value = LitrosG
        wb.SHEETS(1).cells(rng, 3).value = TotalesG
        wb.SHEETS(1).cells(rng, 4).value = OperacionG
        wb.SHEETS(1).cells(rng, 5).value = MuertosG
        wb.SHEETS(1).cells(rng, 6).value = TotalesG / LitrosG
        wb.SHEETS(1).cells(rng, 7).value = SubTotalG
        wb.SHEETS(1).cells(rng, 8).value = IVAG
        wb.SHEETS(1).cells(rng, 9).value = TotalG

        wb.SHEETS(1).columns(1).columnwidth = 13
        wb.SHEETS(1).columns(2).columnwidth = 12
        wb.SHEETS(1).columns(3).columnwidth = 12
        wb.SHEETS(1).columns(2).NumberFormat = "#,##0.0"
        wb.SHEETS(1).columns(3).NumberFormat = "#,##0.0"
        wb.SHEETS(1).columns(4).NumberFormat = "#,##0.0"
        wb.SHEETS(1).columns(5).NumberFormat = "#,##0.0"
        wb.SHEETS(1).columns(6).NumberFormat = "#,##0.0"
        wb.SHEETS(1).columns(7).NumberFormat = "#,##0.00"
        wb.SHEETS(1).columns(8).NumberFormat = "#,##0.00"
        wb.SHEETS(1).columns(9).NumberFormat = "#,##0.00"

        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Bitacora de Diesel del " & Me.datFecha1.Value.ToString("d") & " al " & Me.datFecha2.Value.ToString("d")
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8
    End Sub
    Sub autobus()
        Dim xl As Object
        Dim wb As Object
        Dim i As Integer
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 5

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        'Dim strFecha1 As String = Me.datFecha1.Value.ToString("MM/dd/yyyy") 'String = Format(Me.datFecha1.Value.Year, "0000") & Format(Me.datFecha1.Value.Month, "00") & Format(Me.datFecha1.Value.Day, "00")
        'Dim strFecha2 As String = Me.datFecha2.Value.ToString("MM/dd/yyyy") 'String = Format(Me.datFecha2.Value.Year, "0000") & Format(Me.datFecha2.Value.Month, "00") & Format(Me.datFecha2.Value.Day, "00")
        '--------

        Dim NuevaFecha1 As String = datFecha1.Value.ToString("yyyy-dd-MM")
        Dim NuevaFecha2 As String = datFecha2.Value.AddDays(1).ToString("yyyy-dd-MM")
        NuevaFecha1 = NuevaFecha1 & " 00:00:00"
        NuevaFecha2 = NuevaFecha2 & " 00:00:00"
        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection
        Dim strSql As String = ""
        cnconn.Open()
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn
        Dim cmdAsignar As New SqlCommand
        cmdAsignar.Connection = cnconn

        Dim t As Integer = lstA.Items.Count
        Dim strBUSES As String = "'"

        For i = 0 To t - 2
            strBUSES = strBUSES & Me.lstA.Items(i).ToString.Trim & "','"
        Next

        strBUSES = strBUSES & Me.lstA.Items(t - 1).ToString.Trim & "'"

        strSql = "SELECT IdRuta,NoEconomico,SUM(Litros) as Litros, SUM(KilometrosTotales) as Totales, SUM(KilometrosOperacion) as Operacion, SUM(KilometrosMuertos) as Muertos," & _
                 " SUM(SubTotal) as Subtotal, SUM(IVA) AS Iva, SUM(Total) as Total " & _
                 " FROM Diesel " & _
                 " WHERE Fecha>='" & NuevaFecha1 & "' AND Fecha<='" & NuevaFecha2 & "'" & _
                 " AND NoEconomico IN (" & strBUSES & ")" & _
                 " GROUP BY NoEconomico,IdRuta " & _
                 " ORDER BY IdRuta,NoEconomico"

        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        rdBuscar = cmdBuscar.ExecuteReader
        Dim strFolio As String = "", strSerie As String = ""
        Dim strFecha As String = ""
        Dim strUnidad As String = ""
        Dim acu(999) As Decimal
        Dim strCamion As String = "?"
        Dim strFechaMov As String = ""

        Dim Litros As Double = 0
        Dim Totales As Double = 0
        Dim Operacion As Double = 0
        Dim Muertos As Double = 0
        Dim SubTotal As Double = 0 '
        Dim IVA As Double = 0
        Dim Total As Double = 0
        Dim LitrosG As Double = 0
        Dim TotalesG As Double = 0
        Dim OperacionG As Double = 0
        Dim MuertosG As Double = 0
        Dim SubTotalG As Double = 0 '
        Dim IVAG As Double = 0
        Dim TotalG As Double = 0

        rng = rng + 1
        Do While rdBuscar.Read()
            If strCamion <> rdBuscar("IdRuta").ToString.Trim Then
                If strCamion <> "?" Then
                    wb.SHEETS(1).cells(rng, 1).value = "Totales"
                    wb.SHEETS(1).cells(rng, 2).value = Litros
                    wb.SHEETS(1).cells(rng, 3).value = Totales
                    wb.SHEETS(1).cells(rng, 4).value = Operacion
                    wb.SHEETS(1).cells(rng, 5).value = Muertos
                    wb.SHEETS(1).cells(rng, 6).value = Totales / Litros
                    wb.SHEETS(1).cells(rng, 7).value = SubTotal
                    wb.SHEETS(1).cells(rng, 8).value = IVA
                    wb.SHEETS(1).cells(rng, 9).value = Total

                    Litros = 0
                    Totales = 0
                    Operacion = 0
                    Muertos = 0
                    SubTotal = 0 '
                    IVA = 0
                    Total = 0
                    rng = rng + 2 ' Separa entre ruta
                End If
                wb.SHEETS(1).cells(rng, 1).value = "Ruta: " & BuscarStrDatoCitra("SELECT Nombre FROM Rutas WHERE IdRuta=" & rdBuscar("IdRuta"))
                wb.SHEETS(1).cells(rng, 1).font.size = 12
                wb.SHEETS(1).cells(rng, 1).font.bold = -1
                rng = rng + 1
                wb.SHEETS(1).cells(rng, 1).value = "Autobus"
                wb.SHEETS(1).cells(rng, 2).value = "Litros"
                wb.SHEETS(1).cells(rng, 3).value = "Kms Totales"
                wb.SHEETS(1).cells(rng, 4).value = "Kms Operacion"
                wb.SHEETS(1).cells(rng, 5).value = "Kms Muertos"
                wb.SHEETS(1).cells(rng, 6).value = "Rendimiento"
                wb.SHEETS(1).cells(rng, 7).value = "SubTotal"
                wb.SHEETS(1).cells(rng, 8).value = "IVA"
                wb.SHEETS(1).cells(rng, 9).value = "Total"

                For i = 1 To 9
                    wb.SHEETS(1).cells(rng, i).font.bold = True
                Next
                rng = rng + 1
            End If
            strCamion = rdBuscar("IdRuta").ToString.Trim

            wb.SHEETS(1).cells(rng, 1).value = rdBuscar("NoEconomico")
            wb.SHEETS(1).cells(rng, 2).value = rdBuscar("Litros")
            wb.SHEETS(1).cells(rng, 3).value = rdBuscar("Totales")
            wb.SHEETS(1).cells(rng, 4).value = rdBuscar("Operacion")
            wb.SHEETS(1).cells(rng, 5).value = rdBuscar("Muertos")
            wb.SHEETS(1).cells(rng, 6).value = wb.SHEETS(1).cells(rng, 3).value / wb.SHEETS(1).cells(rng, 2).value
            wb.SHEETS(1).cells(rng, 7).value = rdBuscar("Subtotal")
            wb.SHEETS(1).cells(rng, 8).value = rdBuscar("IVA")
            wb.SHEETS(1).cells(rng, 9).value = rdBuscar("Total")

            Litros = Litros + wb.SHEETS(1).cells(rng, 2).value
            Totales = Totales + wb.SHEETS(1).cells(rng, 3).value
            Operacion = Operacion + wb.SHEETS(1).cells(rng, 4).value
            Muertos = Muertos + wb.SHEETS(1).cells(rng, 5).value
            SubTotal = SubTotal + wb.SHEETS(1).cells(rng, 7).value
            IVA = IVA + wb.SHEETS(1).cells(rng, 8).value
            Total = Total + wb.SHEETS(1).cells(rng, 9).value

            LitrosG = LitrosG + wb.SHEETS(1).cells(rng, 2).value
            TotalesG = TotalesG + wb.SHEETS(1).cells(rng, 3).value
            OperacionG = OperacionG + wb.SHEETS(1).cells(rng, 4).value
            MuertosG = MuertosG + wb.SHEETS(1).cells(rng, 5).value
            SubTotalG = SubTotalG + wb.SHEETS(1).cells(rng, 7).value
            IVAG = IVAG + wb.SHEETS(1).cells(rng, 8).value
            TotalG = TotalG + wb.SHEETS(1).cells(rng, 9).value

            rng = rng + 1
            'End If
        Loop

        wb.SHEETS(1).cells(rng, 1).value = "Totales"
        wb.SHEETS(1).cells(rng, 2).value = Litros
        wb.SHEETS(1).cells(rng, 3).value = Totales
        wb.SHEETS(1).cells(rng, 4).value = Operacion
        wb.SHEETS(1).cells(rng, 5).value = Muertos
        wb.SHEETS(1).cells(rng, 6).value = Totales / Litros
        wb.SHEETS(1).cells(rng, 7).value = SubTotal
        wb.SHEETS(1).cells(rng, 8).value = IVA
        wb.SHEETS(1).cells(rng, 9).value = Total

        rng = rng + 2

        wb.SHEETS(1).cells(rng, 1).value = "Total General"
        wb.SHEETS(1).cells(rng, 2).value = LitrosG
        wb.SHEETS(1).cells(rng, 3).value = TotalesG
        wb.SHEETS(1).cells(rng, 4).value = OperacionG
        wb.SHEETS(1).cells(rng, 5).value = MuertosG
        wb.SHEETS(1).cells(rng, 6).value = TotalesG / LitrosG
        wb.SHEETS(1).cells(rng, 7).value = SubTotalG
        wb.SHEETS(1).cells(rng, 8).value = IVAG
        wb.SHEETS(1).cells(rng, 9).value = TotalG

        wb.SHEETS(1).columns(1).columnwidth = 13
        wb.SHEETS(1).columns(2).columnwidth = 12
        wb.SHEETS(1).columns(3).columnwidth = 12
        wb.SHEETS(1).columns(2).NumberFormat = "#,##0.0"
        wb.SHEETS(1).columns(3).NumberFormat = "#,##0.0"
        wb.SHEETS(1).columns(4).NumberFormat = "#,##0.0"
        wb.SHEETS(1).columns(5).NumberFormat = "#,##0.0"
        wb.SHEETS(1).columns(6).NumberFormat = "#,##0.0"
        wb.SHEETS(1).columns(7).NumberFormat = "#,##0.00"
        wb.SHEETS(1).columns(8).NumberFormat = "#,##0.00"
        wb.SHEETS(1).columns(9).NumberFormat = "#,##0.00"

        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Bitacora de Diesel del " & Me.datFecha1.Value.ToString("d") & " al " & Me.datFecha2.Value.ToString("d")
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8

    End Sub
    Sub ruta()

        Dim xl As Object
        Dim wb As Object
        Dim i As Integer
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 5

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        ' Dim strFecha1 As String = Me.datFecha1.Value.ToString("MM/dd/yyyy") 'String = Format(Me.datFecha1.Value.Year, "0000") & Format(Me.datFecha1.Value.Month, "00") & Format(Me.datFecha1.Value.Day, "00")
        'Dim strFecha2 As String = Me.datFecha2.Value.ToString("MM/dd/yyyy") 'String = Format(Me.datFecha2.Value.Year, "0000") & Format(Me.datFecha2.Value.Month, "00") & Format(Me.datFecha2.Value.Day, "00")
        Dim NuevaFecha1 As String = datFecha1.Value.ToString("yyyy-dd-MM")
        Dim NuevaFecha2 As String = datFecha2.Value.AddDays(1).ToString("yyyy-dd-MM")
        NuevaFecha1 = NuevaFecha1 & " 00:00:00"
        NuevaFecha2 = NuevaFecha2 & " 00:00:00"

        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection
        Dim strSql As String = ""
        cnconn.Open()
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn
        Dim cmdAsignar As New SqlCommand
        cmdAsignar.Connection = cnconn

        Dim clasifi As Integer
        Dim t As Integer = lstR.Items.Count
        Dim strRutas As String = ""

        For i = 0 To t - 2
            clasifi = BuscarIntDatoCitra("Rutas", "IdRuta", "Nombre='" & Me.lstR.Items(i).ToString.Trim & "'")
            strRutas = strRutas & clasifi & ","
        Next
        clasifi = BuscarIntDatoCitra("Rutas", "IdRuta", "Nombre='" & Me.lstR.Items(t - 1).ToString.Trim & "'")
        strRutas = strRutas & clasifi

        strSql = "SELECT IdRuta,NoEconomico,SUM(Litros) as Litros, SUM(KilometrosTotales) as Totales, SUM(KilometrosOperacion) as Operacion, SUM(KilometrosMuertos) as Muertos," & _
                 " SUM(SubTotal) as Subtotal, SUM(IVA) AS Iva, SUM(Total) as Total " & _
                 " FROM Diesel " & _
                 " WHERE Fecha>='" & NuevaFecha1 & "' AND Fecha<='" & NuevaFecha2 & "'" & _
                 " AND IdRuta in (" & strRutas & ")" & _
                 " GROUP BY NoEconomico,IdRuta " & _
                 " ORDER BY IdRuta,NoEconomico"

        cmdBuscar.CommandText = strSql

        Dim rdBuscar As SqlDataReader
        rdBuscar = cmdBuscar.ExecuteReader

        Dim strFolio As String = "", strSerie As String = ""
        Dim strFecha As String = ""
        Dim strUnidad As String = ""
        Dim acu(999) As Decimal
        Dim strCamion As String = "?"
        Dim strFechaMov As String = ""

        Dim Litros As Double = 0
        Dim Totales As Double = 0
        Dim Operacion As Double = 0
        Dim Muertos As Double = 0
        Dim SubTotal As Double = 0 '
        Dim IVA As Double = 0
        Dim Total As Double = 0
        Dim LitrosG As Double = 0
        Dim TotalesG As Double = 0
        Dim OperacionG As Double = 0
        Dim MuertosG As Double = 0
        Dim SubTotalG As Double = 0 '
        Dim IVAG As Double = 0
        Dim TotalG As Double = 0



        rng = rng + 1
        Do While rdBuscar.Read()
            If strCamion <> rdBuscar("IdRuta").ToString.Trim Then
                If strCamion <> "?" Then
                    wb.SHEETS(1).cells(rng, 1).value = "Totales"
                    wb.SHEETS(1).cells(rng, 2).value = Litros
                    wb.SHEETS(1).cells(rng, 3).value = Totales
                    wb.SHEETS(1).cells(rng, 4).value = Operacion
                    wb.SHEETS(1).cells(rng, 5).value = Muertos
                    wb.SHEETS(1).cells(rng, 6).value = Totales / Litros
                    wb.SHEETS(1).cells(rng, 7).value = SubTotal
                    wb.SHEETS(1).cells(rng, 8).value = IVA
                    wb.SHEETS(1).cells(rng, 9).value = Total

                    Litros = 0
                    Totales = 0
                    Operacion = 0
                    Muertos = 0
                    SubTotal = 0 '
                    IVA = 0
                    Total = 0
                    rng = rng + 2 ' Separa entre ruta
                End If
                wb.SHEETS(1).cells(rng, 1).value = "Ruta: " & BuscarStrDatoCitra("SELECT Nombre FROM Rutas WHERE IdRuta=" & rdBuscar("IdRuta"))
                wb.SHEETS(1).cells(rng, 1).font.size = 12
                wb.SHEETS(1).cells(rng, 1).font.bold = -1
                rng = rng + 1
                wb.SHEETS(1).cells(rng, 1).value = "Autobus"
                wb.SHEETS(1).cells(rng, 2).value = "Litros"
                wb.SHEETS(1).cells(rng, 3).value = "Kms Totales"
                wb.SHEETS(1).cells(rng, 4).value = "Kms Operacion"
                wb.SHEETS(1).cells(rng, 5).value = "Kms Muertos"
                wb.SHEETS(1).cells(rng, 6).value = "Rendimiento"
                wb.SHEETS(1).cells(rng, 7).value = "SubTotal"
                wb.SHEETS(1).cells(rng, 8).value = "IVA"
                wb.SHEETS(1).cells(rng, 9).value = "Total"

                For i = 1 To 9
                    wb.SHEETS(1).cells(rng, i).font.bold = True
                Next
                rng = rng + 1
            End If
            strCamion = rdBuscar("IdRuta").ToString.Trim

            wb.SHEETS(1).cells(rng, 1).value = rdBuscar("NoEconomico")
            wb.SHEETS(1).cells(rng, 2).value = rdBuscar("Litros")
            wb.SHEETS(1).cells(rng, 3).value = rdBuscar("Totales")
            wb.SHEETS(1).cells(rng, 4).value = rdBuscar("Operacion")
            wb.SHEETS(1).cells(rng, 5).value = rdBuscar("Muertos")
            wb.SHEETS(1).cells(rng, 6).value = wb.SHEETS(1).cells(rng, 3).value / wb.SHEETS(1).cells(rng, 2).value
            wb.SHEETS(1).cells(rng, 7).value = rdBuscar("Subtotal")
            wb.SHEETS(1).cells(rng, 8).value = rdBuscar("IVA")
            wb.SHEETS(1).cells(rng, 9).value = rdBuscar("Total")

            Litros = Litros + wb.SHEETS(1).cells(rng, 2).value
            Totales = Totales + wb.SHEETS(1).cells(rng, 3).value
            Operacion = Operacion + wb.SHEETS(1).cells(rng, 4).value
            Muertos = Muertos + wb.SHEETS(1).cells(rng, 5).value
            SubTotal = SubTotal + wb.SHEETS(1).cells(rng, 7).value
            IVA = IVA + wb.SHEETS(1).cells(rng, 8).value
            Total = Total + wb.SHEETS(1).cells(rng, 9).value

            LitrosG = LitrosG + wb.SHEETS(1).cells(rng, 2).value
            TotalesG = TotalesG + wb.SHEETS(1).cells(rng, 3).value
            OperacionG = OperacionG + wb.SHEETS(1).cells(rng, 4).value
            MuertosG = MuertosG + wb.SHEETS(1).cells(rng, 5).value
            SubTotalG = SubTotalG + wb.SHEETS(1).cells(rng, 7).value
            IVAG = IVAG + wb.SHEETS(1).cells(rng, 8).value
            TotalG = TotalG + wb.SHEETS(1).cells(rng, 9).value

            rng = rng + 1
            'End If
        Loop

        wb.SHEETS(1).cells(rng, 1).value = "Totales"
        wb.SHEETS(1).cells(rng, 2).value = Litros
        wb.SHEETS(1).cells(rng, 3).value = Totales
        wb.SHEETS(1).cells(rng, 4).value = Operacion
        wb.SHEETS(1).cells(rng, 5).value = Muertos
        wb.SHEETS(1).cells(rng, 6).value = Totales / Litros
        wb.SHEETS(1).cells(rng, 7).value = SubTotal
        wb.SHEETS(1).cells(rng, 8).value = IVA
        wb.SHEETS(1).cells(rng, 9).value = Total

        rng = rng + 2

        wb.SHEETS(1).cells(rng, 1).value = "Total General"
        wb.SHEETS(1).cells(rng, 2).value = LitrosG
        wb.SHEETS(1).cells(rng, 3).value = TotalesG
        wb.SHEETS(1).cells(rng, 4).value = OperacionG
        wb.SHEETS(1).cells(rng, 5).value = MuertosG
        wb.SHEETS(1).cells(rng, 6).value = TotalesG / LitrosG
        wb.SHEETS(1).cells(rng, 7).value = SubTotalG
        wb.SHEETS(1).cells(rng, 8).value = IVAG
        wb.SHEETS(1).cells(rng, 9).value = TotalG

        wb.SHEETS(1).columns(1).columnwidth = 13
        wb.SHEETS(1).columns(2).columnwidth = 12
        wb.SHEETS(1).columns(3).columnwidth = 12
        wb.SHEETS(1).columns(2).NumberFormat = "#,##0.0"
        wb.SHEETS(1).columns(3).NumberFormat = "#,##0.0"
        wb.SHEETS(1).columns(4).NumberFormat = "#,##0.0"
        wb.SHEETS(1).columns(5).NumberFormat = "#,##0.0"
        wb.SHEETS(1).columns(6).NumberFormat = "#,##0.0"
        wb.SHEETS(1).columns(7).NumberFormat = "#,##0.00"
        wb.SHEETS(1).columns(8).NumberFormat = "#,##0.00"
        wb.SHEETS(1).columns(9).NumberFormat = "#,##0.00"

        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Bitacora de Diesel del " & Me.datFecha1.Value.ToString("d") & " al " & Me.datFecha2.Value.ToString("d")
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8
    End Sub

    Private Sub btnAbrir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub RadButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)


    End Sub

    Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
        'Registra actividad
        Bitacora.RegistraActividad("Imprimió el reporte de abastecimientos de diesel del dia " & datFecha1.Text & " al " & datFecha2.Text)
        If RadioButton1.Checked = True Then
            todos()
        ElseIf RadioButton2.Checked = True Then
            ruta()
        Else
            autobus()
        End If

    End Sub

    Private Sub ToolStripButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click
        Close()
    End Sub
End Class